import sqlite3
import dash
from dash import html, dcc
import pandas as pd
from datetime import datetime, timedelta
import requests

DB_PATH = "wechat_group_messages.db"
GEMINI_API_URL = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent"
GEMINI_API_KEY = "AIzaSyD91uzSkCR3bRUdpupNxhE4MbiDEeox0m0"  # 请使用test2.py中的API KEY

app = dash.Dash(__name__)


def get_hourly_messages():
    conn = sqlite3.connect(DB_PATH)

    # 获取最近两天的数据
    end_time = datetime.now()
    start_time = end_time - timedelta(hours=48)

    # 查询重要消息
    query = """
    SELECT 
        chat_name,
        sender,
        content,
        msg_time,
        raw
    FROM important_messages
    WHERE msg_time BETWEEN ? AND ?
    ORDER BY chat_name, msg_time
    """

    df = pd.read_sql_query(query, conn, params=[start_time.strftime('%Y-%m-%d %H:%M:%S'),
                                                end_time.strftime('%Y-%m-%d %H:%M:%S')])
    conn.close()
    
    # 添加日期列
    if not df.empty:
        df['date'] = df['msg_time'].apply(lambda x: x.split()[0] if isinstance(x, str) else datetime.strptime(x, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d'))
    
    return df


def get_participants_by_chat(df):
    # 按群聊名称分组参与者
    participants = {}
    for chat_name, group in df.groupby('chat_name'):
        participants[chat_name] = group['sender'].unique().tolist()
    return participants


def group_messages_by_date_and_hour(df):
    # 按日期、小时和群聊名称分组消息
    if df.empty:
        return {}
        
    # 确保有date列，如果没有，添加一个
    if 'date' not in df.columns:
        df['date'] = df['msg_time'].apply(lambda x: x.split()[0] if isinstance(x, str) else datetime.strptime(x, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d'))
    
    # 添加小时列
    df['hour'] = df['msg_time'].apply(
        lambda x: datetime.strptime(x, '%Y-%m-%d %H:%M:%S').replace(minute=0, second=0).strftime('%Y-%m-%d %H:00:00'))
    df['hour_range'] = df['hour'].apply(
        lambda x: f"{datetime.strptime(x, '%Y-%m-%d %H:00:00').strftime('%H:%M')}-{(datetime.strptime(x, '%Y-%m-%d %H:00:00') + timedelta(hours=1)).strftime('%H:%M')}")

    # 创建嵌套字典来组织数据
    organized_messages = {}
    
    # 按日期、群聊名称和小时范围分组
    for (date, chat_name, hour_range), group in df.groupby(['date', 'chat_name', 'hour_range']):
        if date not in organized_messages:
            organized_messages[date] = {}
            
        if chat_name not in organized_messages[date]:
            organized_messages[date][chat_name] = {}
            
        messages_list = []
        for _, row in group.iterrows():
            messages_list.append({
                'time': row['msg_time'],
                'sender': row['sender'],
                'content': row['content']
            })
            
        organized_messages[date][chat_name][hour_range] = messages_list
    
    return organized_messages


def summarize_messages(messages):
    # 使用Gemini模型对消息进行总结
    try:
        message_text = "\n".join([f"{msg['sender']}: {msg['content']}" for msg in messages])

        headers = {
            "Content-Type": "application/json",
            "x-goog-api-key": GEMINI_API_KEY
        }

        data = {
            "contents": [
                {
                    "parts": [
                        {
                            "text": f"""请对以下微信群聊消息进行总结，简短总结：

{message_text}

总结："""
                        }
                    ]
                }
            ],
            "generationConfig": {
                "temperature": 0.2,
                "maxOutputTokens": 200
            }
        }

        resp = requests.post(GEMINI_API_URL, headers=headers, json=data, timeout=15)
        resp.raise_for_status()
        result = resp.json()
        summary = result["candidates"][0]["content"]["parts"][0]["text"].strip()

        return summary
    except Exception as e:
        print(f"[AI ERROR] 总结消息失败: {e}")
        return "无法生成总结，请检查API配置或网络连接。"


def create_report():
    df = get_hourly_messages()
    if df.empty:
        return html.Div([html.H1('没有找到重要消息')])

    participants = get_participants_by_chat(df)
    organized_messages = group_messages_by_date_and_hour(df)

    report_sections = []
    
    # 按日期组织报告
    for date in sorted(organized_messages.keys()):
        date_section = [html.H1(date)]
        daily_messages = []
        
        # 遍历每个群聊
        for chat_name in organized_messages[date]:
            chat_section = [html.H2(f"群聊<{chat_name}>")]
            chat_participants = participants.get(chat_name, [])
            chat_section.append(html.P(f"参与者： {', '.join(['@' + p for p in chat_participants])}"))
            
            # 遍历每个时间段
            for hour_range in organized_messages[date][chat_name]:
                messages = organized_messages[date][chat_name][hour_range]
                
                # 时间段标题
                time_section = html.H3(f"时间段：{hour_range}")
                
                # 重要消息列表
                message_items = []
                for i, msg in enumerate(messages, 1):
                    message_items.append(html.Li(f"[{msg['time']}] @{msg['sender']}：{msg['content']}"))
                    daily_messages.append(msg)
                
                message_list = html.Div([
                    html.H4("重要消息："),
                    html.Ol(message_items)
                ])
                
                chat_section.extend([time_section, message_list])
            
            # 添加群聊部分到日期部分
            chat_div = html.Div(chat_section, style={
                'marginBottom': '20px', 
                'border': '1px solid #ddd', 
                'padding': '15px', 
                'borderRadius': '5px'
            })
            date_section.append(chat_div)
        
        # 生成当日总结
        if daily_messages:
            summary = summarize_messages(daily_messages)
            summary_section = html.Div([
                html.H3("总结"),
                html.P(summary)
            ], style={
                'marginTop': '20px', 
                'backgroundColor': '#f9f9f9', 
                'padding': '15px', 
                'borderRadius': '5px'
            })
            date_section.append(summary_section)
        
        # 添加整个日期部分
        date_div = html.Div(date_section, style={'marginBottom': '40px'})
        report_sections.append(date_div)
    
    # 添加时间戳
    timestamp = html.P(f"报告生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}",
                       style={'textAlign': 'right', 'color': '#888', 'fontSize': '12px'})
    
    return html.Div([html.H1('幼儿园微信群聊重要消息汇总报告', style={'textAlign': 'center'}), *report_sections, timestamp])


app.layout = html.Div([
    dcc.Interval(
        id='interval-component',
        interval=60 * 60 * 1000,  # 每小时更新一次
        # interval=60 * 1000,     # 测试每分钟刷新一下ok
        n_intervals=0
    ),
    html.Div(id='report-container')
])


@app.callback(
    dash.dependencies.Output('report-container', 'children'),
    dash.dependencies.Input('interval-component', 'n_intervals')
)
def update_report(n):
    return create_report()


if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port=8050)
